﻿'Description:排产-无限产能排产类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh 
'create Date:20220424
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text
Imports External.APICalling

Public Class InfiniteCapacityBusiness
    Private m_containerObj As New uLMSContainerBusiness

    ''' <summary>
    ''' 无限产能排产，数据查询
    ''' add:Wangjh 20220424
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql = <string>SELECT cci.isinstore,CCI.MFGORDERNAME,CCI.CONTAINERID,CCI.CONTAINERNAME,CCI.PRODUCTDESCRIPTION PRODUCTDESC,CCI.PRODUCTREVISION,CCI.QTY,CCI.WORKFLOWSTEPNAME,
CCI.CONTAINERTYPE,DECODE(CCI.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
W.WORKFLOWID,WB.WORKFLOWNAME,W.WORKFLOWREVISION,WB.WORKFLOWNAME||':'||W.WORKFLOWREVISION WORKFLOWINFO,
TO_CHAR(M.PLANNEDSTARTDATE,'YYYY-MM-DD') MFGSTARTDDATE,TO_CHAR(M.PLANNEDCOMPLETIONDATE,'YYYY-MM-DD') MFGENDDDATE,
TO_CHAR(M.PLANNEDSTARTDATE,'YYYY-MM-DD') REALSTARTDATE 
FROM CONTAINERCURRENTINFO CCI
LEFT JOIN MFGORDER M ON M.MFGORDERID=CCI.MFGORDERID
LEFT JOIN ORDERSTATUS o ON o.orderstatusid = M.orderstatusid
LEFT JOIN CONTAINER C ON C.CONTAINERID=CCI.CONTAINERID
left join WORKFLOW W ON W.WORKFLOWID=DECODE(CCI.ISINSTORE,-1,C.MFGWORKFLOWID,CCI.WORKFLOWID)
LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=W.WORKFLOWBASEID
WHERE CCI.CONTAINERSTATUS=1  and cci.isinpool=1 and cci.isinstore!=1 </string>.Value
        strSql += " and (o.orderstatusid IS NULL OR o.ORDERSTATUSNAME <> '关闭') " '订单状态
        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql += String.Format(" and cci.ContainerName like '%{0}%' ", para("ContainerName"))
            End If
        End If

        If para.ContainsKey("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSql += String.Format(" and cci.ProductName like '%{0}%' ", para("ProductName"))
            End If
        End If

        If para.ContainsKey("MfgorderName") Then
            If Not String.IsNullOrWhiteSpace(para("MfgorderName")) Then
                strSql += String.Format(" and cci.MfgorderName like '%{0}%' ", para("MfgorderName"))
            End If
        End If

        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.originalfactoryid ='{0}' ", para("FactoryID"))
            End If
        End If

        If para.ContainsKey("OrderStartDate") Then
            If Not String.IsNullOrWhiteSpace(para("OrderStartDate")) Then
                strSql += String.Format(" and M.plannedstartdate >= to_date('{0}','YYYY-MM-DD') ", para("OrderStartDate"))
            End If
        End If

        If para.ContainsKey("OrderEndDate") Then
            If Not String.IsNullOrWhiteSpace(para("OrderEndDate")) Then
                strSql += String.Format(" and M.plannedcompletiondate <= to_date('{0}','YYYY-MM-DD') ", para("OrderEndDate"))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
        '如果已经在制了，开始时间则是当前时间
        For Each row As DataRow In result.DBTable.Rows
            If row("isinstore").ToString = "0" Then
                row("REALSTARTDATE") = Date.Now.ToString("yyyy-MM-dd")
            End If
        Next
        Return result
    End Function

    ''' <summary>
    ''' 获取工序级的排产信息
    ''' yuxj20220815增加qty列，甘特图调整排产时用
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfiniteAps(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql = <string> select cci.qty, ai.ApsType,ai.containerid,ai.apsplaninfoid,cci.containername,ai.starttime constarttime,ai.endtime conendtime,ai.workflowid,ad.apsplandetailid,ad.workflowstepid,ad.workflowstepname,
  ad.starttime stepstarttime,ad.endtime stependtime from ApsPlanInfo ai 
  left join containercurrentinfo cci on cci.containerid=ai.containerid
  left join ApsPlanDetail ad on ad.apsplaninfoid=ai.apsplaninfoid and ad.isuse=0
  where 1=1 and ai.isuse=0 and ai.ApsType2=1 </string>.Value
        If para.ContainsKey("ApsPlanDetailID") Then
            strSql += String.Format(" and ad.ApsPlanDetailID='{0}' ", para("ApsPlanDetailID"))
        End If
        If para.ContainsKey("ApsPlanInfoID") Then
            strSql += String.Format(" and ad.ApsPlanInfoID='{0}' ", para("ApsPlanInfoID"))
        End If

        If para.ContainsKey("ApsPlanInfoIDs") Then
            strSql += String.Format(" and ad.ApsPlanInfoID in ({0}) ", para("ApsPlanInfoIDs"))
        End If

        If para.ContainsKey("ContainerID") Then
            strSql += String.Format(" and ad.ContainerID='{0}' ", para("ContainerID"))
        End If

        If para.ContainsKey("WorkflowStepID") Then
            strSql += String.Format(" and ad.WorkflowStepID='{0}' ", para("WorkflowStepID"))
        End If

        strSql += " order by ai.containerid,ai.workflowid,ad.workflowstepid "

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 获取批次的无限排产信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerInfiniteAps(ByVal paraDt As DataTable, Optional ByVal type As String = "") As DataTable
        Dim strSql = <string> select ai.apsplaninfoid,ai.containerid,TO_CHAR(ai.starttime,'YYYY-MM-DD HH24:MI:SS') starttime,TO_CHAR(ai.endtime,'YYYY-MM-DD HH24:MI:SS') endtime,ai.ApsType
 from APSPLANINFO ai where 1=1 and ai.ApsType2=1 </string>.Value

        If Not String.IsNullOrWhiteSpace(type) Then
            strSql += " and ai.ApsType=" + type
        End If

        Dim condition As String = ""
        For Each r In paraDt.Rows
            condition += " (ai.containerid='" + r("containerid").ToString() + "'"
            condition += " and ai.workflowid='" + r("workflowid").ToString() + "'"

            condition += ") or"
        Next
        condition = condition.Trim("r", "o")

        strSql += " and ( " + condition + " ) "
        Dim containerDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 无限产能，倒排计算
    ''' </summary>
    ''' <param name="paraDt"></param>
    ''' <remarks></remarks>
    Public Sub ReverseCalculate(ByVal paraDt As DataTable, ByVal para As Dictionary(Of String, String))
        Dim stepSql = <stirng>SELECT nvl(ws.shipcycle,0) shipcycle,nvl(ws.transfercycle,0) transfercycle,nvl(ws.makecycle,0) makecycle,
nvl(ws.preparecycle,0) preparecycle,nvl(ws.commicycle,0) commicycle,ws.workflowstepname,ws.workflowstepid,ws.workflowid,
decode(s.factoryid,w.factoryid,0,1) isship,nvl(ws.controlcode,s.controlcode) controlcode  from workflow w
left join workflowstep ws on ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
where w.workflowid='{0}' order by ws.workflowstepid </stirng>.Value

        Dim makeSql = "select * from MakeCalendarDetail ml where ml.MakeCalendarID='{0}' and ml.isuse=0 "
        Dim makeCalendarDt = DatabaseHelper.Query_ReturnDatTable(String.Format(makeSql, para("MakeCalendarID")))

        '批次级排产信息sql
        Dim conSelectSql = <string> select * from ApsPlanInfo ai where ai.containerid='{0}' and ai.workflowid='{1}' and ai.ApsType2=1 and ai.ApsType=2 and ai.Isuse=0 </string>.Value
        Dim conInsertSql = <STIRNG> insert into ApsPlanInfo ai ( ai.ApsType2,ai.apsplaninfoid,ai.apstype,ai.containerid,ai.createdate,ai.createemployeeid,ai.starttime,ai.isuse,
 ai.lastchangedate,ai.notes,ai.endtime,ai.workflowid) values ( 1,'{0}','{1}','{2}',sysdate,'{3}',to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,
 SYSDATE,'{5}',to_date('{6}','YYYY-MM-DD HH24:MI:SS'),'{7}') </STIRNG>.Value
        Dim conUpdateSql = <string>update ApsPlanInfo ai set ai.endtime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ai.starttime=to_date('{1}','YYYY-MM-DD HH24:MI:SS'), 
 ai.lastchangedate=sysdate where ai.apsplaninfoid='{2}'</string>.Value

        '工序级排产信息sql
        Dim stepSelectSql = <string>select * from ApsPlanDetail ad where ad.apsplaninfoid='{0}' and ad.ContainerID='{1}' and ad.isuse=0 </string>.Value
        Dim stepInsertSql = <string> insert into ApsPlanDetail ad (ad.apsplandetailid,ad.apsplaninfoid,ad.containerid,ad.endtime,ad.starttime,ad.isuse,ad.workflowstepid,ad.workflowstepname,ad.notes,ad.createdate)
 values ('{0}','{1}','{2}',to_date('{3}','YYYY-MM-DD HH24:MI:SS'),to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,'{5}','{6}','{7}',SYSDATE) </string>.Value
        Dim stepUpdateSql = <string> update ApsPlanDetail ad set ad.endtime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ad.starttime=to_date('{1}','YYYY-MM-DD HH24:MI:SS')
  where ad.apsplaninfoid='{2}' and ad.workflowstepid='{3}' </string>.Value

        For Each r In paraDt.Rows
            Dim endTime = Date.Parse(r("ENDTIME"))

            '判断结束日期是否为假期
            Dim conoffset = 0 '日期偏移量
            While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", endTime.AddDays(-conoffset).Date)).Length > 0
                conoffset += 1
            End While
            endTime = endTime.AddDays(-conoffset)

            Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSql, r("workflowid").ToString)) '工序
            '如果批次工序已经完工，筛选掉完工的工序
            For i As Integer = stepDt.Rows.Count - 1 To 0 Step -1
                If m_containerObj.JudgeContainerStepIsFinish(r("containerid").ToString, stepDt.Rows(i)("workflowstepid").ToString).Result Then
                    stepDt.Rows.RemoveAt(i)
                End If
            Next
            stepDt.AcceptChanges()

            Dim curTime = endTime
            If endTime = endTime.Date Then
                curTime = endTime.AddDays(1).AddSeconds(-60) '倒排时时间为 23：59：00
                endTime = curTime
            End If

            'yuxj20220818为处理甘特图保存出错修改qty赋值
            Dim qty = -1
            If (paraDt.Columns.Contains("qty")) Then
                qty = Integer.Parse(r("qty").ToString)
            ElseIf qty = -1 Then
                Dim strsql As String = "select cc.qty from containercurrentinfo cc where cc.containerid='" + r("containerid").ToString + "'"
                Dim dtqty As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
                qty = dtqty.Rows(0)("qty").ToString
            End If
            Dim stepCycle = 0 '工序加工周期，单位分钟
            Dim stepPlanSqls As New ArrayList
            Dim conApsID = Guid.NewGuid.ToString
            Dim conApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(conSelectSql, r("containerid").ToString, r("workflowid").ToString)) '批次的计划信息
            If conApsDt.Rows.Count > 0 Then
                conApsID = conApsDt.Rows(0)("ApsPlanInfoID").ToString
            End If
            Dim stepApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSelectSql, conApsID, r("containerid").ToString)) '批次工序的计划信息

            For j As Integer = stepDt.Rows.Count - 1 To 0 Step -1
                Dim stepRow = stepDt.Rows(j)
                stepCycle = 0 '工序加工周期，单位分钟
                Dim _endTime = curTime

                If stepRow("isship").ToString = "1" > 0 Then '转工序 
                    stepCycle = Val(stepRow("shipcycle").ToString) * 24 * 60
                ElseIf stepRow("controlcode").ToString = "LY05" Then '外委序
                    stepCycle = Val(stepRow("commicycle").ToString) * 24 * 60
                Else '本车间加工序
                    stepCycle = Integer.Parse(stepRow("transfercycle").ToString) + qty * Integer.Parse(stepRow("makecycle").ToString) +
                         Integer.Parse(stepRow("preparecycle").ToString)
                End If

                curTime = curTime.AddMinutes(-stepCycle)
                Dim differDays = (_endTime.Date - curTime.Date).Days
                If differDays > 0 Then

                    Dim offset = 0 '偏移量
                    For i As Integer = 1 To differDays
                        While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", _endTime.AddDays(-(i + offset)).Date)).Length > 0
                            offset += 1
                        End While
                    Next
                    curTime = curTime.AddDays(-offset)

                End If

                '生成工序计划sql
                If stepApsDt.Select("workflowstepid='" + stepRow("workflowstepid").ToString + "'").Length > 0 Then
                    stepPlanSqls.Add(String.Format(stepUpdateSql, _endTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   conApsID, stepRow("workflowstepid").ToString))
                Else
                    stepPlanSqls.Add(String.Format(stepInsertSql, Guid.NewGuid.ToString(), conApsID, r("containerid").ToString, _endTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   stepRow("workflowstepid").ToString, stepRow("workflowstepname").ToString, ""))
                End If
            Next
            '生成批次计划sql
            If conApsDt.Rows.Count > 0 Then
                stepPlanSqls.Add(String.Format(conUpdateSql, endTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"), conApsID))
            Else
                stepPlanSqls.Add(String.Format(conInsertSql, conApsID, 2, r("containerid").ToString, para("EmployeeID"), curTime.ToString("yyyy-MM-dd HH:mm:ss"), "", endTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                               r("workflowid").ToString))
            End If

            '此批次包括工序的计划
            DatabaseHelper.ExecuteSqlTran(stepPlanSqls)
        Next

    End Sub

    ''' <summary>
    ''' 无限产能，正排计算
    ''' </summary>
    ''' <param name="paraDt"></param>
    ''' <remarks></remarks>
    Public Sub PositiveCalculate(ByVal paraDt As DataTable, ByVal para As Dictionary(Of String, String))

        Dim stepSql = <stirng>SELECT nvl(ws.shipcycle,0) shipcycle,nvl(ws.transfercycle,0) transfercycle,nvl(ws.makecycle,0) makecycle,
nvl(ws.preparecycle,0) preparecycle,nvl(ws.commicycle,0) commicycle,ws.workflowstepname,ws.workflowstepid,ws.workflowid,ws.sequence,
decode(s.factoryid,w.factoryid,0,1) isship,nvl(ws.controlcode,s.controlcode) controlcode  from workflow w
left join workflowstep ws on ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
where w.workflowid='{0}' order by ws.workflowstepid </stirng>.Value

        Dim makeSql = "select * from MakeCalendarDetail ml where ml.MakeCalendarID='{0}' and ml.isuse=0 "
        Dim makeCalendarDt = DatabaseHelper.Query_ReturnDatTable(String.Format(makeSql, para("MakeCalendarID")))

        '批次级排产信息sql
        Dim conSelectSql = <string> select * from ApsPlanInfo ai where ai.containerid='{0}' and ai.workflowid='{1}' and ai.ApsType2=1 and ai.ApsType=1 and ai.Isuse=0 </string>.Value
        Dim conInsertSql = <STIRNG> insert into ApsPlanInfo ai (ai.ApsType2,ai.apsplaninfoid,ai.apstype,ai.containerid,ai.createdate,ai.createemployeeid,ai.endtime,ai.isuse,
 ai.lastchangedate,ai.notes,ai.starttime,ai.workflowid) values (1,'{0}','{1}','{2}',sysdate,'{3}',to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,
 SYSDATE,'{5}',to_date('{6}','YYYY-MM-DD HH24:MI:SS'),'{7}') </STIRNG>.Value
        Dim conUpdateSql = <string>update ApsPlanInfo ai set ai.starttime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ai.endtime=to_date('{1}','YYYY-MM-DD HH24:MI:SS'), 
 ai.lastchangedate=sysdate where ai.apsplaninfoid='{2}'</string>.Value

        '工序级排产信息sql
        Dim stepSelectSql = <string>select * from ApsPlanDetail ad where ad.apsplaninfoid='{0}' and ad.ContainerID='{1}' and ad.isuse=0 </string>.Value
        Dim stepInsertSql = <string> insert into ApsPlanDetail ad (ad.apsplandetailid,ad.apsplaninfoid,ad.containerid,ad.starttime,ad.endtime,ad.isuse,ad.workflowstepid,ad.workflowstepname,ad.notes,ad.createdate)
 values ('{0}','{1}','{2}',to_date('{3}','YYYY-MM-DD HH24:MI:SS'),to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,'{5}','{6}','{7}',SYSDATE) </string>.Value
        Dim stepUpdateSql = <string> update ApsPlanDetail ad set ad.starttime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ad.endtime=to_date('{1}','YYYY-MM-DD HH24:MI:SS')
  where ad.apsplaninfoid='{2}' and ad.workflowstepid='{3}' </string>.Value

        '查询无限产能排产池 add by zhangrj 20250725
        Dim dtApsPool = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>
                                        SELECT ap.infinitecycle FROM apspool ap WHERE ap.factoryid = '{0}' AND ap.isuse = 0 AND ap.apspooltype = '2'
                                    </string>.Value, para("FactoryID")))
        '排产截止日期
        Dim deadlineDate As Date = New Date(9999, 1, 1)
        If dtApsPool.Rows.Count > 0 Then
            deadlineDate = Date.Now.Date.AddDays(Val(dtApsPool.Rows(0)("infinitecycle").ToString()) + 1)
        End If

        For Each r In paraDt.Rows
            Dim startTime = Date.Parse(r("STARTTIME")) '批次开始日期
            Dim entTime = startTime '批次结束日期
            '判断开始日期是否为假期
            Dim conoffset = 0 '日期偏移量
            While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", startTime.AddDays(conoffset).Date)).Length > 0
                conoffset += 1
            End While
            startTime = startTime.AddDays(conoffset)

            Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSql, r("workflowid").ToString)) '工序
            '如果批次工序已经完工，筛选掉完工的工序
            For i As Integer = stepDt.Rows.Count - 1 To 0 Step -1
                If m_containerObj.JudgeContainerStepIsFinish(r("containerid").ToString, stepDt.Rows(i)("workflowstepid").ToString).Result Then
                    stepDt.Rows.RemoveAt(i)
                End If
            Next
            stepDt.AcceptChanges()

            Dim curTime = startTime

            'yuxj20220818为处理甘特图保存出错修改qty赋值
            Dim qty = -1
            If (paraDt.Columns.Contains("qty")) Then
                qty = Integer.Parse(r("qty").ToString)
            ElseIf qty = -1 Then
                '同一批次查询一次
                Dim strsql As String = "select cc.qty from containercurrentinfo cc where cc.containerid='" + r("containerid").ToString + "'"
                Dim dtqty As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
                qty = dtqty.Rows(0)("qty").ToString
            End If

            Dim stepCycle = 0 '工序加工周期，单位分钟
            Dim stepPlanSqls As New ArrayList
            Dim conApsID = Guid.NewGuid.ToString
            Dim conApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(conSelectSql, r("containerid").ToString, r("workflowid").ToString)) '批次的计划信息
            If conApsDt.Rows.Count > 0 Then
                conApsID = conApsDt.Rows(0)("ApsPlanInfoID").ToString
            End If
            Dim stepApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSelectSql, conApsID, r("containerid").ToString)) '批次工序的计划信息

            For Each stepRow As DataRow In stepDt.Rows
                stepCycle = 0 '工序加工周期，单位分钟
                Dim _startTime = curTime

                If stepRow("isship").ToString = "1" > 0 Then '转工序
                    stepCycle = Val(stepRow("shipcycle").ToString) * 24 * 60
                ElseIf stepRow("controlcode").ToString = "LY05" Then '外委序
                    stepCycle = Val(stepRow("commicycle").ToString) * 24 * 60
                Else '本车间加工序
                    stepCycle = Single.Parse(stepRow("transfercycle").ToString) + qty * Single.Parse(stepRow("makecycle").ToString) +
                         Single.Parse(stepRow("preparecycle").ToString)
                End If

                '如果产生隔天，判断隔天后的日期是否是节假日
                curTime = curTime.AddMinutes(stepCycle)
                Dim differDays = (curTime.Date - _startTime.Date).Days
                If differDays > 0 Then

                    Dim offset = 0 '偏移量
                    For i As Integer = 1 To differDays
                        While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", _startTime.AddDays(i + offset).Date)).Length > 0
                            offset += 1
                        End While
                    Next
                    curTime = curTime.AddDays(offset)
                End If

                '如果结束日期大于截止日期，则结束该批次的排产 add by zhangrj 20250725
                If curTime >= deadlineDate Then
                    '到截止日期后，若后续的工序还有以前的排产数据，则应该清除
                    If conApsDt.Rows.Count > 0 Then
                        DeleteApsPlanData(conApsID, Integer.Parse(stepRow("sequence").ToString))
                    End If
                    Exit For
                End If
                entTime = curTime '更新结束日期
                '生成工序计划sql
                If stepApsDt.Select("workflowstepid='" + stepRow("workflowstepid").ToString + "'").Length > 0 Then
                    stepPlanSqls.Add(String.Format(stepUpdateSql, _startTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   conApsID, stepRow("workflowstepid").ToString))
                Else
                    stepPlanSqls.Add(String.Format(stepInsertSql, Guid.NewGuid.ToString(), conApsID, r("containerid").ToString, _startTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   stepRow("workflowstepid").ToString, stepRow("workflowstepname").ToString, ""))
                End If
            Next

            '生成批次计划sql
            If conApsDt.Rows.Count > 0 Then
                stepPlanSqls.Add(String.Format(conUpdateSql, startTime.ToString("yyyy-MM-dd HH:mm:ss"), entTime.ToString("yyyy-MM-dd HH:mm:ss"), conApsID))
            Else
                stepPlanSqls.Add(String.Format(conInsertSql, conApsID, 1, r("containerid").ToString, para("EmployeeID"), entTime.ToString("yyyy-MM-dd HH:mm:ss"), "", startTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                               r("workflowid").ToString))
            End If

            '此批次包括工序的计划
            DatabaseHelper.ExecuteSqlTran(stepPlanSqls)
        Next

    End Sub

    ''' <summary>
    ''' 删除排产数据
    ''' </summary>
    ''' <param name="conApsID">排产ID</param>
    ''' <param name="seq">工序顺序</param>
    ''' <remarks></remarks>
    Public Sub DeleteApsPlanData(ByVal conApsID As String, ByVal seq As Integer, Optional ByVal status As String = "")
        Dim strSql = <string> select al.apsplandetailid from ApsPlanDetail al 
                    left join workflowstep ws on ws.workflowstepid=al.workflowstepid
                    where al.apsplaninfoid='{0}' and al.isuse=0 and nvl(al.status,'0')='0' and ws.sequence>={1} </string>.Value
        If Not String.IsNullOrWhiteSpace(status) Then
            strSql += " and al.status='" + status + "'"
        End If
        Dim sqlList As New ArrayList

        Dim apsDetailDt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, conApsID, seq))

        For Each row In apsDetailDt.Rows
            sqlList.Add(String.Format("delete apsplandetail al where al.apsplandetailid='{0}'", row("apsplandetailid").ToString))
        Next

        DatabaseHelper.ExecuteSqlTran(sqlList)

        If DatabaseHelper.GetSingle("select 1 from ApsPlanDetail al where al.apsplaninfoid='" + conApsID + "'") Is Nothing Then
            DatabaseHelper.ExecuteSql("delete ApsPlanInfo where ApsPlanInfoid='" + conApsID + "'")
        End If

    End Sub

    ''' <summary>
    ''' 无限产能，正排计算（预排）
    ''' </summary>
    ''' <param name="paraDt"></param>
    ''' <remarks></remarks>
    Public Sub LnPositiveCalculate(ByVal paraDt As DataTable, ByVal para As Dictionary(Of String, String))

        Dim stepSql = <stirng>SELECT nvl(ws.shipcycle,0) shipcycle,nvl(ws.transfercycle,0) transfercycle,nvl(ws.makecycle,0) makecycle,
nvl(ws.preparecycle,0) preparecycle,nvl(ws.commicycle,0) commicycle,ws.workflowstepname,ws.workflowstepid,ws.workflowid,
decode(s.factoryid,w.factoryid,0,1) isship,nvl(ws.controlcode,s.controlcode) controlcode  from workflow w
left join workflowstep ws on ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
where w.workflowid='{0}' order by ws.workflowstepid </stirng>.Value

        Dim makeSql = "select * from MakeCalendarDetail ml where ml.MakeCalendarID='{0}' and ml.isuse=0 "
        Dim makeCalendarDt = DatabaseHelper.Query_ReturnDatTable(String.Format(makeSql, para("MakeCalendarID")))

        '批次级排产信息sql
        Dim conSelectSql = <string> select * from ApsPlanInfopre ai where ai.containerid='{0}' and ai.workflowid='{1}' and ai.ApsType2=1 and ai.ApsType=1 and ai.Isuse=0 </string>.Value
        Dim conInsertSql = <STIRNG> insert into ApsPlanInfopre ai (ai.ApsType2,ai.apsplaninfoid,ai.apstype,ai.containerid,ai.createdate,ai.createemployeeid,ai.endtime,ai.isuse,
 ai.lastchangedate,ai.notes,ai.starttime,ai.workflowid) values (1,'{0}','{1}','{2}',sysdate,'{3}',to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,
 SYSDATE,'{5}',to_date('{6}','YYYY-MM-DD HH24:MI:SS'),'{7}') </STIRNG>.Value
        Dim conUpdateSql = <string>update ApsPlanInfopre ai set ai.starttime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ai.endtime=to_date('{1}','YYYY-MM-DD HH24:MI:SS'), 
 ai.lastchangedate=sysdate where ai.apsplaninfoid='{2}'</string>.Value

        '工序级排产信息sql
        Dim stepSelectSql = <string>select * from ApsPlanDetailpre ad where ad.apsplaninfoid='{0}' and ad.ContainerID='{1}' and ad.isuse=0 </string>.Value
        Dim stepInsertSql = <string> insert into ApsPlanDetailpre ad (ad.apsplandetailid,ad.apsplaninfoid,ad.containerid,ad.starttime,ad.endtime,ad.isuse,ad.workflowstepid,ad.workflowstepname,ad.notes)
 values ('{0}','{1}','{2}',to_date('{3}','YYYY-MM-DD HH24:MI:SS'),to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,'{5}','{6}','{7}') </string>.Value
        Dim stepUpdateSql = <string> update ApsPlanDetailpre ad set ad.starttime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ad.endtime=to_date('{1}','YYYY-MM-DD HH24:MI:SS')
  where ad.apsplaninfoid='{2}' and ad.workflowstepid='{3}' </string>.Value


        For Each r In paraDt.Rows
            Dim startTime = Date.Parse(r("STARTTIME"))
            '判断开始日期是否为假期
            Dim conoffset = 0 '日期偏移量
            While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", startTime.AddDays(conoffset).Date)).Length > 0
                conoffset += 1
            End While
            startTime = startTime.AddDays(conoffset)

            Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSql, r("workflowid").ToString)) '工序
            '如果批次工序已经完工，筛选掉完工的工序
            For i As Integer = stepDt.Rows.Count - 1 To 0 Step -1
                If m_containerObj.JudgeContainerStepIsFinish(r("containerid").ToString, stepDt.Rows(i)("workflowstepid").ToString).Result Then
                    stepDt.Rows.RemoveAt(i)
                End If
            Next
            stepDt.AcceptChanges()

            Dim curTime = startTime

            'yuxj20220818为处理甘特图保存出错修改qty赋值
            Dim qty = -1
            If (paraDt.Columns.Contains("qty")) Then
                qty = Integer.Parse(r("qty").ToString)
            ElseIf qty = -1 Then
                '同一批次查询一次
                Dim strsql As String = "select cc.qty from containercurrentinfo cc where cc.containerid='" + r("containerid").ToString + "'"
                Dim dtqty As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
                qty = dtqty.Rows(0)("qty").ToString
            End If

            Dim stepCycle = 0 '工序加工周期，单位分钟
            Dim stepPlanSqls As New ArrayList
            Dim conApsID = Guid.NewGuid.ToString
            Dim conApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(conSelectSql, r("containerid").ToString, r("workflowid").ToString)) '批次的计划信息
            If conApsDt.Rows.Count > 0 Then
                conApsID = conApsDt.Rows(0)("ApsPlanInfoID").ToString
            End If
            Dim stepApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSelectSql, conApsID, r("containerid").ToString)) '批次工序的计划信息

            For Each stepRow As DataRow In stepDt.Rows
                stepCycle = 0 '工序加工周期，单位分钟
                Dim _startTime = curTime

                If stepRow("isship").ToString = "1" > 0 Then '转工序
                    stepCycle = Val(stepRow("shipcycle").ToString) * 24 * 60
                ElseIf stepRow("controlcode").ToString = "LY05" Then '外委序
                    stepCycle = Val(stepRow("commicycle").ToString) * 24 * 60
                Else '本车间加工序
                    stepCycle = Integer.Parse(stepRow("transfercycle").ToString) + qty * Integer.Parse(stepRow("makecycle").ToString) +
                         Integer.Parse(stepRow("preparecycle").ToString)
                End If

                '如果产生隔天，判断隔天后的日期是否是节假日
                curTime = curTime.AddMinutes(stepCycle)
                Dim differDays = (curTime.Date - _startTime.Date).Days
                If differDays > 0 Then

                    Dim offset = 0 '偏移量
                    For i As Integer = 1 To differDays
                        While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", _startTime.AddDays(i + offset).Date)).Length > 0
                            offset += 1
                        End While
                    Next
                    curTime = curTime.AddDays(offset)

                End If

                '生成工序计划sql
                If stepApsDt.Select("workflowstepid='" + stepRow("workflowstepid").ToString + "'").Length > 0 Then
                    stepPlanSqls.Add(String.Format(stepUpdateSql, _startTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   conApsID, stepRow("workflowstepid").ToString))
                Else
                    stepPlanSqls.Add(String.Format(stepInsertSql, Guid.NewGuid.ToString(), conApsID, r("containerid").ToString, _startTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   stepRow("workflowstepid").ToString, stepRow("workflowstepname").ToString, ""))
                End If
            Next

            '生成批次计划sql
            If conApsDt.Rows.Count > 0 Then
                stepPlanSqls.Add(String.Format(conUpdateSql, startTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"), conApsID))
            Else
                stepPlanSqls.Add(String.Format(conInsertSql, conApsID, 1, r("containerid").ToString, para("EmployeeID"), curTime.ToString("yyyy-MM-dd HH:mm:ss"), "", startTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                               r("workflowid").ToString))
            End If

            '此批次包括工序的计划
            DatabaseHelper.ExecuteSqlTran(stepPlanSqls)
        Next

    End Sub

    ''' <summary>
    ''' 无限产能，倒排计算（预排）
    ''' </summary>
    ''' <param name="paraDt"></param>
    ''' <remarks></remarks>
    Public Sub LnReverseCalculate(ByVal paraDt As DataTable, ByVal para As Dictionary(Of String, String))
        Dim stepSql = <stirng>SELECT nvl(ws.shipcycle,0) shipcycle,nvl(ws.transfercycle,0) transfercycle,nvl(ws.makecycle,0) makecycle,
nvl(ws.preparecycle,0) preparecycle,nvl(ws.commicycle,0) commicycle,ws.workflowstepname,ws.workflowstepid,ws.workflowid,
decode(s.factoryid,w.factoryid,0,1) isship,nvl(ws.controlcode,s.controlcode) controlcode  from workflow w
left join workflowstep ws on ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
where w.workflowid='{0}' order by ws.workflowstepid </stirng>.Value

        Dim makeSql = "select * from MakeCalendarDetail ml where ml.MakeCalendarID='{0}' and ml.isuse=0 "
        Dim makeCalendarDt = DatabaseHelper.Query_ReturnDatTable(String.Format(makeSql, para("MakeCalendarID")))

        '批次级排产信息sql
        Dim conSelectSql = <string> select * from ApsPlanInfopre ai where ai.containerid='{0}' and ai.workflowid='{1}' and ai.ApsType2=1 and ai.ApsType=2 and ai.Isuse=0 </string>.Value
        Dim conInsertSql = <STIRNG> insert into ApsPlanInfopre ai ( ai.ApsType2,ai.apsplaninfoid,ai.apstype,ai.containerid,ai.createdate,ai.createemployeeid,ai.starttime,ai.isuse,
 ai.lastchangedate,ai.notes,ai.endtime,ai.workflowid) values ( 1,'{0}','{1}','{2}',sysdate,'{3}',to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,
 SYSDATE,'{5}',to_date('{6}','YYYY-MM-DD HH24:MI:SS'),'{7}') </STIRNG>.Value
        Dim conUpdateSql = <string>update ApsPlanInfopre ai set ai.endtime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ai.starttime=to_date('{1}','YYYY-MM-DD HH24:MI:SS'), 
 ai.lastchangedate=sysdate where ai.apsplaninfoid='{2}'</string>.Value

        '工序级排产信息sql
        Dim stepSelectSql = <string>select * from ApsPlanDetailpre ad where ad.apsplaninfoid='{0}' and ad.ContainerID='{1}' and ad.isuse=0 </string>.Value
        Dim stepInsertSql = <string> insert into ApsPlanDetailpre ad (ad.apsplandetailid,ad.apsplaninfoid,ad.containerid,ad.endtime,ad.starttime,ad.isuse,ad.workflowstepid,ad.workflowstepname,ad.notes)
 values ('{0}','{1}','{2}',to_date('{3}','YYYY-MM-DD HH24:MI:SS'),to_date('{4}','YYYY-MM-DD HH24:MI:SS'),0,'{5}','{6}','{7}') </string>.Value
        Dim stepUpdateSql = <string> update ApsPlanDetailpre ad set ad.endtime=to_date('{0}','YYYY-MM-DD HH24:MI:SS') , ad.starttime=to_date('{1}','YYYY-MM-DD HH24:MI:SS')
  where ad.apsplaninfoid='{2}' and ad.workflowstepid='{3}' </string>.Value

        For Each r In paraDt.Rows
            Dim endTime = Date.Parse(r("ENDTIME"))

            '判断结束日期是否为假期
            Dim conoffset = 0 '日期偏移量
            While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", endTime.AddDays(-conoffset).Date)).Length > 0
                conoffset += 1
            End While
            endTime = endTime.AddDays(-conoffset)

            Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSql, r("workflowid").ToString)) '工序
            '如果批次工序已经完工，筛选掉完工的工序
            For i As Integer = stepDt.Rows.Count - 1 To 0 Step -1
                If m_containerObj.JudgeContainerStepIsFinish(r("containerid").ToString, stepDt.Rows(i)("workflowstepid").ToString).Result Then
                    stepDt.Rows.RemoveAt(i)
                End If
            Next
            stepDt.AcceptChanges()

            Dim curTime = endTime
            If endTime = endTime.Date Then
                curTime = endTime.AddDays(1).AddSeconds(-60) '倒排时时间为 23：59：00
                endTime = curTime
            End If

            'yuxj20220818为处理甘特图保存出错修改qty赋值
            Dim qty = -1
            If (paraDt.Columns.Contains("qty")) Then
                qty = Integer.Parse(r("qty").ToString)
            ElseIf qty = -1 Then
                Dim strsql As String = "select cc.qty from containercurrentinfo cc where cc.containerid='" + r("containerid").ToString + "'"
                Dim dtqty As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
                qty = dtqty.Rows(0)("qty").ToString
            End If
            Dim stepCycle = 0 '工序加工周期，单位分钟
            Dim stepPlanSqls As New ArrayList
            Dim conApsID = Guid.NewGuid.ToString
            Dim conApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(conSelectSql, r("containerid").ToString, r("workflowid").ToString)) '批次的计划信息
            If conApsDt.Rows.Count > 0 Then
                conApsID = conApsDt.Rows(0)("ApsPlanInfoID").ToString
            End If
            Dim stepApsDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(stepSelectSql, conApsID, r("containerid").ToString)) '批次工序的计划信息

            For j As Integer = stepDt.Rows.Count - 1 To 0 Step -1
                Dim stepRow = stepDt.Rows(j)
                stepCycle = 0 '工序加工周期，单位分钟
                Dim _endTime = curTime

                If stepRow("isship").ToString = "1" > 0 Then '转工序 
                    stepCycle = Val(stepRow("shipcycle").ToString) * 24 * 60
                ElseIf stepRow("controlcode").ToString = "LY05" Then '外委序
                    stepCycle = Val(stepRow("commicycle").ToString) * 24 * 60
                Else '本车间加工序
                    stepCycle = Integer.Parse(stepRow("transfercycle").ToString) + qty * Integer.Parse(stepRow("makecycle").ToString) +
                         Integer.Parse(stepRow("preparecycle").ToString)
                End If

                curTime = curTime.AddMinutes(-stepCycle)
                Dim differDays = (_endTime.Date - curTime.Date).Days
                If differDays > 0 Then

                    Dim offset = 0 '偏移量
                    For i As Integer = 1 To differDays
                        While makeCalendarDt.Select(String.Format("Holidaydate='{0}'", _endTime.AddDays(-(i + offset)).Date)).Length > 0
                            offset += 1
                        End While
                    Next
                    curTime = curTime.AddDays(-offset)

                End If

                '生成工序计划sql
                If stepApsDt.Select("workflowstepid='" + stepRow("workflowstepid").ToString + "'").Length > 0 Then
                    stepPlanSqls.Add(String.Format(stepUpdateSql, _endTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   conApsID, stepRow("workflowstepid").ToString))
                Else
                    stepPlanSqls.Add(String.Format(stepInsertSql, Guid.NewGuid.ToString(), conApsID, r("containerid").ToString, _endTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                                   stepRow("workflowstepid").ToString, stepRow("workflowstepname").ToString, ""))
                End If
            Next
            '生成批次计划sql
            If conApsDt.Rows.Count > 0 Then
                stepPlanSqls.Add(String.Format(conUpdateSql, endTime.ToString("yyyy-MM-dd HH:mm:ss"), curTime.ToString("yyyy-MM-dd HH:mm:ss"), conApsID))
            Else
                stepPlanSqls.Add(String.Format(conInsertSql, conApsID, 2, r("containerid").ToString, para("EmployeeID"), curTime.ToString("yyyy-MM-dd HH:mm:ss"), "", endTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                               r("workflowid").ToString))
            End If

            '此批次包括工序的计划
            DatabaseHelper.ExecuteSqlTran(stepPlanSqls)
        Next

    End Sub


    ''' <summary>
    ''' 调整无限产能排产
    ''' </summary>
    ''' <param name="conAps"></param>
    ''' <remarks></remarks>
    Public Sub AdjustInfiniteCalculate(ByVal conAps As DataTable, ByVal para As Dictionary(Of String, String))
        Dim positiveDt As New DataTable
        If conAps.Select("apsType=1").Length > 0 Then
            positiveDt = conAps.Select("apsType=1").CopyToDataTable()
        End If
        Dim reverseDt As New DataTable
        If conAps.Select("apsType=2").Length > 0 Then
            reverseDt = conAps.Select("apsType=2").CopyToDataTable
        End If
        PositiveCalculate(positiveDt, para)
        ReverseCalculate(reverseDt, para)
    End Sub
End Class
